using System;
using System.Collections.Generic;
using System.Drawing;
using FarPoint.Win.Spread;

namespace Spread.Sparkline.Features
{
    public partial class BoxPlotSparkline : Spread.Common.Features.Sample.BaseSampleControl
    {
        public BoxPlotSparkline()
        {
            InitializeComponent();
            Name = "BoxPlot";

            List<Color> colors = new List<Color>
            {
                Color.White,
                Color.Black,
                Color.Orange,
                Color.LightGray,
                Color.Gray,
                Color.DarkGray,
                Color.Green
            };
            comboSchemeColor.DataSource = colors;
            comboSchemeColor.SelectedItem = Color.LightGray;

            txtStartScope.Text = "0";
            txtEndScope.Text = "100";
            txtStartExpectedScope.Text = "20";
            txtEndExpectedScope.Text = "80";

            InitSpread();

            btnChangeSettings.Click += BtnChangeSettings_Click;
        }

        #region Private methods
        private void InitSpread()
        {
            fpSpread1.Sheets.Count = 2;
            fpSpread1.TabStripInsertTab = false;

            InitHorizontalSparkline(fpSpread1.Sheets[0], "Horizontal");
            InitVerticalSparkline(fpSpread1.Sheets[1], "Vertical");
        }

        private void InitHorizontalSparkline(SheetView sheetView, string name)
        {
            var worksheet = sheetView.AsWorksheet();
            worksheet.Name = name;

            worksheet.Cells["B2:O2"].Merge();
            worksheet.Cells["B2"].Value = "The Company Sales in 2014";
            worksheet.Cells["B2"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 16 });
            worksheet.Cells["B2"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            sheetView.Cells["B2"].ForeColor = Color.White;
            sheetView.Cells["B2"].BackColor = ColorTranslator.FromHtml("#999999");

            worksheet.Cells["B3:O3"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["B3:O3"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 12, Bold = true });
            sheetView.Cells["B3:O3"].BackColor = ColorTranslator.FromHtml("#efefef");

            worksheet.Cells["B3"].Value = "Region";
            worksheet.Cells["O3"].Value = "Actual Sales (mn)";

            for (int i = 2; i < 14; i++)
            {
                char columnChar = Convert.ToChar(i + 65);
                worksheet.Cells[$"{columnChar}3"].Value = new DateTime(2014, i - 1, 1);
                worksheet.Cells[$"{columnChar}3"].NumberFormat = "mm";
            }

            worksheet.SetValue(3, 1, new object[,]
            {
                { "Alabama", 68, 81, 21, 69, 39, 28, 63, 73, 80, 95, 46, 37 },
                { "Alaska", 37, 57, 99, 47, 95, 86, 44, 82, 89, 61, 45, 75 },
                { "Arizona", 61, 42, 97, 12, 95, 25, 29, 44, 64, 39, 59, 35 },
                { "Idaho", 16, 51, 45, 82, 72, 51, 100, 98, 92, 89, 44, 68 },
                { "Indiana", 26, 46, 74, 58, 40, 32, 58, 75, 39, 58, 97, 39 },
                { "Ohio", 37, 68, 20, 35, 65, 87, 39, 25, 79, 15, 91, 42 },
                { "Oklahoma", 36, 43, 31, 63, 95, 26, 19, 88, 25, 78, 98, 53 },
                { "Oregon", 28, 71, 68, 48, 19, 56, 88, 54, 28, 25, 92, 75 },
                { "Vermon", 85, 25, 31, 34, 75, 82, 50, 27, 76, 72, 25, 100 }
            });

            for (int index = 3; index < 12; index++)
            {
                worksheet.Cells[$"O{index + 1}"].Formula2 = "BOXPLOTSPARKLINE($C" + (index + 1) + ":$N" + (index + 1) + ",\"5ns\",true,0,100,20,80,\"#D3D3D3\",0,false)";
            }

            worksheet.Rows["2:12"].RowHeight = 30;
            worksheet.Columns["O"].ColumnWidth = 250;
        }

        private void InitVerticalSparkline(SheetView sheetView, string name)
        {
            var worksheet = sheetView.AsWorksheet();
            worksheet.Name = name;

            worksheet.Cells["B2:E2"].Merge();
            worksheet.Cells["B2"].Value = "Scores by Teaching Method";
            worksheet.Cells["B2"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["B2"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 11, Bold = true });
            sheetView.Cells["B2"].ForeColor = Color.White;
            sheetView.Cells["B2"].BackColor = ColorTranslator.FromHtml("#999999");

            worksheet.Cells["B3:E3"].VerticalAlignment = GrapeCity.Spreadsheet.VerticalAlignment.Center;
            worksheet.Cells["B3:E3"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 11, Bold = true });
            sheetView.Cells["B3:E3"].BackColor = ColorTranslator.FromHtml("#efefef");

            worksheet.Cells["B3"].Value = "Student ID";
            worksheet.Cells["C3"].Value = "Method 1";
            worksheet.Cells["D3"].Value = "Method 2";
            worksheet.Cells["E3"].Value = "Method 3";

            worksheet.Cells["G24"].Value = "Method 1";
            worksheet.Cells["H24"].Value = "Method 2";
            worksheet.Cells["I24"].Value = "Method 3";
            worksheet.Cells["G24:I24"].Font.ApplyFont(new GrapeCity.Spreadsheet.Font() { Name = "Arial", Size = 9, Bold = true });

            var scores = new List<dynamic>
            {
                new { Id = 1, MethodOne = 65, MethodTwo = 60, MethodThree = 98 },
                new { Id = 2, MethodOne = 91, MethodTwo = 70, MethodThree = 99 },
                new { Id = 3, MethodOne = 70, MethodTwo = 86, MethodThree = 92 },
                new { Id = 4, MethodOne = 62, MethodTwo = 62, MethodThree = 78 },
                new { Id = 5, MethodOne = 98, MethodTwo = 79, MethodThree = 71 },
                new { Id = 6, MethodOne = 89, MethodTwo = 99, MethodThree = 68 },
                new { Id = 7, MethodOne = 85, MethodTwo = 100, MethodThree = 88 },
                new { Id = 8, MethodOne = 65, MethodTwo = 78, MethodThree = 74 },
                new { Id = 9, MethodOne = 65, MethodTwo = 66, MethodThree = 85 },
                new { Id = 10, MethodOne = 65, MethodTwo = 84, MethodThree = 80 },
                new { Id = 11, MethodOne = 65, MethodTwo = 98, MethodThree = 79 },
                new { Id = 12, MethodOne = 65, MethodTwo = 87, MethodThree = 50 },
                new { Id = 13, MethodOne = 51, MethodTwo = 68, MethodThree = 64 },
                new { Id = 14, MethodOne = 45, MethodTwo = 78, MethodThree = 73 },
                new { Id = 15, MethodOne = 67, MethodTwo = 81, MethodThree = 88 },
                new { Id = 16, MethodOne = 87, MethodTwo = 83, MethodThree = 85 },
                new { Id = 17, MethodOne = 72, MethodTwo = 84, MethodThree = 84 },
                new { Id = 18, MethodOne = 74, MethodTwo = 82, MethodThree = 86 },
                new { Id = 19, MethodOne = 85, MethodTwo = 45, MethodThree = 92 },
                new { Id = 20, MethodOne = 65, MethodTwo = 60, MethodThree = 69 }
            };
            for (int i = 0; i < scores.Count; i++)
            {
                dynamic student = scores[i];
                worksheet.Cells[$"B{i + 4}"].Value = student.Id;
                worksheet.Cells[$"C{i + 4}"].Value = student.MethodOne;
                worksheet.Cells[$"D{i + 4}"].Value = student.MethodTwo;
                worksheet.Cells[$"E{i + 4}"].Value = student.MethodThree;
            }
            worksheet.Columns["B:E"].ColumnWidth = 100;

            worksheet.Cells["G4:G23"].Merge();
            worksheet.Cells["H4:H23"].Merge();
            worksheet.Cells["I4:I23"].Merge();

            worksheet.Cells["G4"].Formula2 = "BOXPLOTSPARKLINE(C4:C23,\"5ns\",true,0,100,20,80,\"#DDDDDD\",0,true)";
            worksheet.Cells["H4"].Formula2 = "BOXPLOTSPARKLINE(D4:D23,\"5ns\",true,0,100,20,80,\"#DDDDDD\",0,true)";
            worksheet.Cells["I4"].Formula2 = "BOXPLOTSPARKLINE(E4:E23,\"5ns\",true,0,100,20,80,\"#DDDDDD\",0,true)";
        }
        #endregion

        #region Event handlers
        private void BtnChangeSettings_Click(object sender, EventArgs e)
        {
            var worksheet = fpSpread1.ActiveSheet.AsWorksheet();

            if (fpSpread1.ActiveSheetIndex == 0)
            {
                for (int index = 3; index < 12; index++)
                {
                    worksheet.Cells[$"O{index+1}"].Formula2 = "BOXPLOTSPARKLINE($C" + (index + 1) + ":$N" + (index + 1) + ",\"5ns\",true," +
                        txtStartScope.Text + "," + txtEndScope.Text + "," + txtStartExpectedScope.Text + "," + txtEndExpectedScope.Text +
                        ",\"" + ((Color)comboSchemeColor.SelectedItem).Name + "\",0,FALSE)";
                }
            }
            else if (fpSpread1.ActiveSheetIndex == 1)
            {
                worksheet.Cells["G4"].Formula2 = "BOXPLOTSPARKLINE(C4:C23,\"5ns\",true," +
                    txtStartScope.Text + "," + txtEndScope.Text + "," + txtStartExpectedScope.Text + "," + txtEndExpectedScope.Text +
                    ",\"" + ((Color)comboSchemeColor.SelectedItem).Name + "\",0,TRUE)";

                worksheet.Cells["H4"].Formula2 = "BOXPLOTSPARKLINE(D4:D23,\"5ns\",true," +
                    txtStartScope.Text + "," + txtEndScope.Text + "," + txtStartExpectedScope.Text + "," + txtEndExpectedScope.Text +
                    ",\"" + ((Color)comboSchemeColor.SelectedItem).Name + "\",0,TRUE)";

                worksheet.Cells["I4"].Formula2 = "BOXPLOTSPARKLINE(E4:E23,\"5ns\",true," +
                    txtStartScope.Text + "," + txtEndScope.Text + "," + txtStartExpectedScope.Text + "," + txtEndExpectedScope.Text +
                    ",\"" + ((Color)comboSchemeColor.SelectedItem).Name + "\",0,TRUE)";
            }
        }
        #endregion
    }
}


//
//BoxPlotSparkline.Designer.cs
//



namespace Spread.Sparkline.Features
{
    partial class BoxPlotSparkline
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(BoxPlotSparkline));
            this.fpSpread1 = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None, ((object)(resources.GetObject("pnlMain.Controls"))));
            this.fpSpread1_Sheet1 = this.fpSpread1.GetSheet(0);
            this.groupBox1 = new System.Windows.Forms.GroupBox();
            this.txtEndExpectedScope = new System.Windows.Forms.TextBox();
            this.txtStartExpectedScope = new System.Windows.Forms.TextBox();
            this.txtEndScope = new System.Windows.Forms.TextBox();
            this.txtStartScope = new System.Windows.Forms.TextBox();
            this.btnChangeSettings = new System.Windows.Forms.Button();
            this.comboSchemeColor = new System.Windows.Forms.ComboBox();
            this.label5 = new System.Windows.Forms.Label();
            this.label4 = new System.Windows.Forms.Label();
            this.label3 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.label1 = new System.Windows.Forms.Label();
            this.pnlMain.SuspendLayout();
            this.pnlControls.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).BeginInit();
            this.groupBox1.SuspendLayout();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread1);
            // 
            // pnlControls
            // 
            this.pnlControls.Controls.Add(this.groupBox1);
            this.pnlControls.Size = new System.Drawing.Size(210, 599);
            // 
            // fpSpread1
            // 
            this.fpSpread1.AccessibleDescription = "Book1, Sheet1, Row 0, Column 0";
            this.fpSpread1.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread1.Font = new System.Drawing.Font("Calibri", 11F);
            this.fpSpread1.Location = new System.Drawing.Point(0, 0);
            this.fpSpread1.Name = "fpSpread1";
            this.fpSpread1.Size = new System.Drawing.Size(636, 599);
            this.fpSpread1.TabIndex = 2;
            // 
            // groupBox1
            // 
            this.groupBox1.Controls.Add(this.txtEndExpectedScope);
            this.groupBox1.Controls.Add(this.txtStartExpectedScope);
            this.groupBox1.Controls.Add(this.txtEndScope);
            this.groupBox1.Controls.Add(this.txtStartScope);
            this.groupBox1.Controls.Add(this.btnChangeSettings);
            this.groupBox1.Controls.Add(this.comboSchemeColor);
            this.groupBox1.Controls.Add(this.label5);
            this.groupBox1.Controls.Add(this.label4);
            this.groupBox1.Controls.Add(this.label3);
            this.groupBox1.Controls.Add(this.label2);
            this.groupBox1.Controls.Add(this.label1);
            this.groupBox1.Dock = System.Windows.Forms.DockStyle.Top;
            this.groupBox1.Location = new System.Drawing.Point(0, 0);
            this.groupBox1.Name = "groupBox1";
            this.groupBox1.Size = new System.Drawing.Size(210, 367);
            this.groupBox1.TabIndex = 1;
            this.groupBox1.TabStop = false;
            this.groupBox1.Text = "Settings";
            // 
            // txtEndExpectedScope
            // 
            this.txtEndExpectedScope.Location = new System.Drawing.Point(12, 229);
            this.txtEndExpectedScope.Name = "txtEndExpectedScope";
            this.txtEndExpectedScope.Size = new System.Drawing.Size(146, 22);
            this.txtEndExpectedScope.TabIndex = 14;
            // 
            // txtStartExpectedScope
            // 
            this.txtStartExpectedScope.Location = new System.Drawing.Point(12, 172);
            this.txtStartExpectedScope.Name = "txtStartExpectedScope";
            this.txtStartExpectedScope.Size = new System.Drawing.Size(146, 22);
            this.txtStartExpectedScope.TabIndex = 13;
            // 
            // txtEndScope
            // 
            this.txtEndScope.Location = new System.Drawing.Point(12, 115);
            this.txtEndScope.Name = "txtEndScope";
            this.txtEndScope.Size = new System.Drawing.Size(146, 22);
            this.txtEndScope.TabIndex = 12;
            // 
            // txtStartScope
            // 
            this.txtStartScope.Location = new System.Drawing.Point(12, 58);
            this.txtStartScope.Name = "txtStartScope";
            this.txtStartScope.Size = new System.Drawing.Size(146, 22);
            this.txtStartScope.TabIndex = 11;
            // 
            // btnChangeSettings
            // 
            this.btnChangeSettings.AutoSize = true;
            this.btnChangeSettings.Location = new System.Drawing.Point(12, 321);
            this.btnChangeSettings.Name = "btnChangeSettings";
            this.btnChangeSettings.Size = new System.Drawing.Size(122, 27);
            this.btnChangeSettings.TabIndex = 10;
            this.btnChangeSettings.Text = "Change Settings";
            this.btnChangeSettings.UseVisualStyleBackColor = true;
            // 
            // comboSchemeColor
            // 
            this.comboSchemeColor.FormattingEnabled = true;
            this.comboSchemeColor.Location = new System.Drawing.Point(113, 271);
            this.comboSchemeColor.Name = "comboSchemeColor";
            this.comboSchemeColor.Size = new System.Drawing.Size(104, 24);
            this.comboSchemeColor.TabIndex = 9;
            // 
            // label5
            // 
            this.label5.AutoSize = true;
            this.label5.Location = new System.Drawing.Point(9, 274);
            this.label5.Name = "label5";
            this.label5.Size = new System.Drawing.Size(98, 17);
            this.label5.TabIndex = 8;
            this.label5.Text = "Color scheme:";
            // 
            // label4
            // 
            this.label4.AutoSize = true;
            this.label4.Location = new System.Drawing.Point(9, 203);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(186, 17);
            this.label4.TabIndex = 6;
            this.label4.Text = "End scope of expected sale:";
            // 
            // label3
            // 
            this.label3.AutoSize = true;
            this.label3.Location = new System.Drawing.Point(9, 146);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(191, 17);
            this.label3.TabIndex = 4;
            this.label3.Text = "Start scope of expected sale:";
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(9, 89);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(149, 17);
            this.label2.TabIndex = 2;
            this.label2.Text = "End scope of the sale:";
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(9, 32);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(154, 17);
            this.label1.TabIndex = 0;
            this.label1.Text = "Start scope of the sale:";
            // 
            // BoxPlotSparkline
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "BoxPlotSparkline";
            this.pnlMain.ResumeLayout(false);
            this.pnlControls.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread1)).EndInit();
            this.groupBox1.ResumeLayout(false);
            this.groupBox1.PerformLayout();
            this.ResumeLayout(false);

        }

        #endregion
        private FarPoint.Win.Spread.FpSpread fpSpread1;
        private FarPoint.Win.Spread.SheetView fpSpread1_Sheet1;
        private System.Windows.Forms.GroupBox groupBox1;
        private System.Windows.Forms.TextBox txtEndExpectedScope;
        private System.Windows.Forms.TextBox txtStartExpectedScope;
        private System.Windows.Forms.TextBox txtEndScope;
        private System.Windows.Forms.TextBox txtStartScope;
        private System.Windows.Forms.Button btnChangeSettings;
        private System.Windows.Forms.ComboBox comboSchemeColor;
        private System.Windows.Forms.Label label5;
        private System.Windows.Forms.Label label4;
        private System.Windows.Forms.Label label3;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.Label label1;
    }
}
